<?php

class Model_sales extends CI_Model{
	function __construct(){
        parent::__construct();
	}
	
	
	
	
	function forgot_password(){ // save time and generate secret key, then return the secret key
	}
	
	


	
	function getSOList(){
		$this->db->select("*");	
		$this->db->from('so');
		$this->db->join('customer','customer.customer_id = so.cust_id');
		$this->db->join('pt','pt.pt_id = so.pt_id');
		$this->db->join('user','user.user_id= so.salesid');
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$hasil = $query->result_array();
			return $hasil;
		}else{
			return array();
		}
	}
	
	/*function getSODet($so){
		$this->db->select("*");		
		$this->db->from("so");
		$this->db->join('pt as pt','pt.pt_id= invoice.pt_id');
		$this->db->join('customer as cust','cust.customer_id= invoice.cust_id');
		$this->db->where("so_no", $so);
		$query = $this->db->get();
		if($query->num_rows()>0){
			$hasil = $query->result_array();
			return $hasil;
		}
	}*/
	
	function getSODet($invo){
		$this->db->select("so.*,pt.*,customer.*,
						   customerbill.id AS billid, customerbill.bill_name AS billname,
						   customerdelivery.id AS delivid, customerdelivery.ship_name AS delivname,
						   user.name AS namasales,
						   user.user_id AS uganda");	
		$this->db->from('so', 'customer', 'pt','customerbill','customerdelivery','user');
		$this->db->join('customer','customer.customer_id = so.cust_id', 'left');
		$this->db->join('pt','pt.pt_id = so.pt_id', 'left');
		$this->db->join('customerbill','customerbill.id = so.bill', 'left');
		$this->db->join('customerdelivery','customerdelivery.id = so.deliv', 'left');
		$this->db->join('user','user.user_id = so.salesid');
		$this->db->where("so_no", $invo);
		$query = $this->db->get();
		if($query->num_rows()>0){
			$hasil = $query->result_array();
			return $hasil;
		}
	}
	
	
	function getItem($so){
		$this->db->select("*");	
		$this->db->from('so_item');
		$this->db->where("so_id", $so);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$hasil = $query->result_array();
			return $hasil;
		}else{
			return array();
		}
	}
	
	function delete($user_id){
		$data = array(
		   'is_active' => 'N'
		);
		
		$this->db->where('user_id', $user_id);
		$this->db->update('user', $data); 
	}
	function changepassword($newpassword){
		$password = md5($newpassword);
		$data = array(
               'password' => $password
            );

		$this->db->where('user_id', $this->session->userdata("user_id"));
		$this->db->update('user', $data);
	}
	
	
	function ajaxGetSearch($jenis, $nilai){
		$this->db->select("*");
		
		$this->db->from("so");
		$this->db->join('pt as pt','pt.pt_id= so.pt_id');
		$this->db->join('customer as cust','cust.customer_id= so.cust_id');
		$this->db->join('user as us','us.user_id= so.salesid');
		if(($nilai=="")||($nilai==" ")){
			
		}
		else{
			if($jenis=="nomor"){
				$this->db->where("invoice_no", $nilai);
				
			}else if($jenis=="customer"){
				$this->db->like('pt_name', $nilai, 'both');
				 
			}else if($jenis=="sales"){
				$this->db->like('pt_name', $nilai, 'both');
			}else{}
		}
		
		
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}
	}
	
	function addBayar($bayar, $tanggal, $melalui, $noInvo, $total, $duedate){
		$datenow = strtotime(date('Y-m-d'));
		$dateDuecompare =strtotime($duedate);
		if($bayar==$total){
			$status = "Lunas";
			$dueStatus = "Lunas";
		}else if($bayar==0){
			$status = "Belum Bayar";
			if($dateDuecompare<$datenow){
				$dueStatus = "Terlambat";
			}else{
				$dueStatus = "Belum Terlambat";
			}
		}else if($bayar<$total){
			$status = "Belum Lunas";
			if($dateDuecompare<$datenow){
				$dueStatus = "Terlambat";
			}else{
				$dueStatus = "Belum Terlambat";
			}
		}
		$datax = array(
		   	'bayar' =>$bayar,
			'paymethod' =>$melalui,
			'tglBayar' => $tanggal,
			'status' =>$status,
			'due' =>$dueStatus
			
		);
		$this->db->where('invoice_no', $noInvo);
		$this->db->update('invoice', $datax); 
			
	}
	
	function getDataInvo($noinvoice){
		$this->db->from('invoice', 'customer', 'pt');
		$this->db->join('customer','customer.customer_id = invoice.cust_id');
		$this->db->join('pt','pt.pt_id = invoice.pt_id');
		$this->db->where("invoice.invoice_no", $noinvoice);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$hasil = $query->result_array();
			return $hasil;
		}else{
			return array();
		}
	}
	/*
			=================Function Add Invoice=================
	*/
	function addSO($status='mobil masih disewa'){
		
		$no_so = $this->model_sales->checkso();
		
		
		$numberTotal = str_replace(".","",$this->input->post("subTotal"));
		$numberBayar = str_replace(".","",$this->input->post("deposit"));  
		$dateCreate = strtotime($this->input->post("dateNow",true));
		$dateDue = strtotime($this->input->post("dateDue",true));
		$dateDeliv = strtotime($this->input->post("delivDate",true));
		
		$dateCreate1 = date('Y-m-d', $dateCreate);
		$dateDue1 = date('Y-m-d', $dateDue);
		$dateDeliv1 = date('Y-m-d', $dateDeliv);
		$datenow = strtotime(date('Y-m-d'));
		$dateDuecompare =strtotime($dateDue1);
		
		if($numberBayar==$numberTotal){
			$status = "Lunas";
			$dueStatus = "Lunas";
		}else if($numberBayar==0){
			$status = "Belum Bayar";
			if($dateDuecompare<$datenow){
				$dueStatus = "Terlambat";
			}else{
				$dueStatus = "Belum Terlambat";
			}
		}else if($numberBayar<$numberTotal){
			$status = "Belum Lunas";
			if($dateDuecompare<$datenow){
				$dueStatus = "Terlambat";
			}else{
				$dueStatus = "Belum Terlambat";
			}
		}
		
		
		
		$data = array(
		   
		   'emplo_id' => $this->session->userdata("user_id"),
		   'cust_id' => $this->input->post("customer",true),
		   'pt_id' => $this->input->post("ptName",true) ,
		   'so_no' => $no_so,
		   'so_date' => $dateCreate1,
		   'note' => $this->input->post("note"),
		   'currency' => $this->input->post("mataUang"),
		   'bill' => $this->input->post("billto"),
		   'deliv' => $this->input->post("shipto"),
		   'delivdate' => $dateDeliv1,
		   'duedate' => $dateDue1,
		   'salesid' => $this->input->post("salesPerson"),
		   'term' => $this->input->post("payterm"),
		   'delivterms' => $this->input->post("delivTerm"),
		   'paymethod' => $this->input->post("payMethod"),
		   'total' => $numberTotal,
		   'bayar' => $numberBayar,
		   'status' => $status,	
		   'due' => $dueStatus,		   
		   'orderno' => $this->input->post("order")
		);
		
		$this->db->insert('so', $data); 
		$id_so = $this->db->insert_id('so', $data);
		$hitung = $this->input->post("counter", true);
		for($i=1;$i<=$hitung;$i++){
			if($this->input->post("hargabrg_".$i, true)==""){}
			else{
				//$prices = number_format($this->input->post("hargabrg_".$i, true), 0, '', ''); 
				
				$dollarValue = $this->input->post("hargabrg_".$i, true); //example of ornery value
				$value = strval(strip_tags($dollarValue)); //figured it couldn't hurt to make it string
				$myval = htmlentities($value); //important part
				$prices = str_replace(array('&amp;#36; ', '.'), '', $myval); //remove $ and comma
				
				$data1 = array(
					'so_id' => $no_so,
					'brg_id' => $this->input->post("kodebrg_".$i, true),
					'brgprt_id' => $this->input->post("kodebrgprt_".$i, true),
					'harga' => $prices,
					'desc' =>  $this->input->post("descbrg_".$i, true),
					'prd' => $this->input->post("q_".$i, true),
					'disc' =>  $this->input->post("disc_".$i, true),
					'tax1' => $this->input->post("tax1_".$i, true),
					'tax2' =>  $this->input->post("tax2_".$i, true)
				);
				$this->db->insert('so_item', $data1); 
			}
		}
		//$this->model_invoice->updateSewa($id_mobil, $id_invo, $tanggal_pinjam, $tanggal_kembali);
	}
	
	function checkSO(){
		$this->db->select("so_no");
		$this->db->order_by("so_no", "desc");
		
		$this->db->from("so");
		$this->db->like('so_no', 'SO'.date("ym"), 'after');
		$query = $this->db->get();
		$query2 = $query->row_array();
		
		if($query->num_rows==0)
		{			
			
			$jumlah = date("ym")*1000 + 1;
			$jumlah = "SO". $jumlah;
			
			return $jumlah;
		}else{
			$angka = explode("SO", $query2['so_no']);
			$jumlah = $angka[1]+1;
			$jumlah = "SO". $jumlah;
			return $jumlah;			
		}
	}
	function getPtList(){
		$this->db->select("*");
		$this->db->from("pt");
		$this->db->order_by("pt_name asc");
		$query = $this->db->get();
		if($query->num_rows()>0){
			$hasil = $query->result_array();
			return $hasil;
		}
	}
	function getCustomerList(){
		$this->db->select("*");
		$this->db->from("customer");
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}
	}
	function getStaffList(){
		$this->db->select("*");
		$this->db->from("user");
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}
	}
	function getPayTerms(){
		$this->db->select("*");
		$this->db->order_by("days asc");
		$this->db->from("payterms");
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}
	}
	function addPayTerm($bookn, $booki){
		$datax = array(
		   	'days' =>$bookn,
			'desc' => $booki
		);
		$this->db->insert('payterms', $datax); 
			
	}
	function addDelivTo($name, $telp, $fax, $alamat, $rt, $rw, $kode, $kota, $provinsi, $cuId, $ptId){
		$datax = array(
		   	'customerId' =>$cuId,
			'ship_name' => $name,
			'ship_perusahaan' =>$ptId,
			'ship_telp' => $telp,
			'ship_fax' =>$fax,
			'ship_alamat' => $alamat,
			'ship_rt' =>$rt,
			'ship_rw' => $rw,
			'ship_kode' =>$kode,
			'ship_kota' => $kota,
			'ship_provinsi' =>$provinsi
		);
		$this->db->insert('customerdelivery', $datax); 
			
	}
	function addBillTo($name, $telp, $fax, $alamat, $rt, $rw, $kode, $kota, $provinsi, $cuId, $ptId){
		$datax = array(
		   	'customerId' =>$cuId,
			'bill_name' => $name,
			'bill_perusahaan' =>$ptId,
			'bill_telp' => $telp,
			'bill_fax' =>$fax,
			'bill_alamat' => $alamat,
			'bill_rt' =>$rt,
			'bill_rw' => $rw,
			'bill_kode' =>$kode,
			'bill_kota' => $kota,
			'bill_provinsi' =>$provinsi
		);
		$this->db->insert('customerbill', $datax); 
			
	}
	function ajaxGetCustomerList($ptId, $mode){
		$this->db->select("*");
		$this->db->from("customer");
		$this->db->where("customer_pt_id", $ptId);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}
	}
	function ajaxGetCustomerDeliv($cuId){
		$this->db->select("*");
		$this->db->from("customerdelivery");
		$this->db->where("customerId", $cuId);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}
	}
	function ajaxGetCustomerBill($cuId){
		$this->db->select("*");
		$this->db->from("customerbill");
		$this->db->where("customerId", $cuId);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}
	}
	
	function ajaxGetBarangSupir(){
		$data   =   array(
                    'drivers.driver_id   as CID',
                    'drivers.*',
                    '(select group_concat(driver_price.harga) from driver_price where driver_price.driverprice_id = CID) as harga',
					'(select group_concat(driver_price.kode) from driver_price where driver_price.driverprice_id = CID) as kode',
					'(select group_concat(driver_price.keterangan) from driver_price where driver_price.driverprice_id = CID) as keterangan'
							           
                    );
    $this->db->select($data);
	
    //$this->db->group_by('cars.name');
    $this->db->from('driver as drivers');
    $this->db->join('driver_price as dp','dp.driverprice_id = drivers.driver_id','left');
    $this->db->group_by('drivers.driver_id');
	return $this->db->get('driver')->result();
		//$this->db->select('car.*, GROUP_CONCAT(DISTINCT car_id), GROUP_CONCAT(carprice_id)', FALSE);
		//$this->db->from('car', 'car_price');
		//$this->db->join('car_price','carprice_id = car_id', 'left');
		//$this->db->select("*");
		//$this->db->group_by('car_id');
		
		/*if($query->num_rows() > 0){
			return $this->db->get('car')->result();
		}else{
			return array();
		}*/
		
		/*$this->db->select("*");
		$this->db->from("driver");
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$data = array();
			$res = $query->result_array();
			foreach ($res as $hasil){
				$data[] = $hasil;

			}
			return $data;
		}else{
			return array();
		}*/
	}
	
	function ajaxGetBarangMobil(){
		$data   =   array(
                    'cars.car_id   as CID',
                    'cars.*',
                    '(select group_concat(car_price.price) from car_price where car_price.carprice_id = CID) as price',
					'(select group_concat(car_price.kode) from car_price where car_price.carprice_id = CID) as kode',
					'(select group_concat(car_price.keterangan) from car_price where car_price.carprice_id = CID) as keterangan'
							           
                    );
    $this->db->select($data);
	
    //$this->db->group_by('cars.name');
    $this->db->from('car as cars');
    $this->db->join('car_price as cp','cp.carprice_id = cars.car_id','left');
    $this->db->group_by('cars.car_license');
	return $this->db->get('car')->result();
		//$this->db->select('car.*, GROUP_CONCAT(DISTINCT car_id), GROUP_CONCAT(carprice_id)', FALSE);
		//$this->db->from('car', 'car_price');
		//$this->db->join('car_price','carprice_id = car_id', 'left');
		//$this->db->select("*");
		//$this->db->group_by('car_id');
		
		/*if($query->num_rows() > 0){
			return $this->db->get('car')->result();
		}else{
			return array();
		}*/
		
	}
	function check_perusahaan($nama){
		$this->db->select("pt_id");
		$this->db->from("pt");
		$this->db->where("pt_name",$nama);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$query2 = $query->result_array();
			$hasil = $query2[0]['pt_id'];
			return $hasil;
		}else{
			$data = array(
		   		'pt_name' => $nama
			);
			$this->db->insert('pt', $data); 
			return $this->db->insert_id('pt', $data);
		}
	}
	
	function check_orang_perusahaan($pt_id, $orang, $telepon){
		$this->db->select("customer_id");
		$this->db->from("customer");
		$this->db->where("customer_pt_id", $pt_id);
		$this->db->where("customer_name", $orang);
		$this->db->where("customer_telp", $telepon);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$query2 = $query->result_array();
			$hasil = $query2[0]['customer_id'];
			return $hasil;
		}else{
			$data = array(
		   		'customer_name' => $orang,
				'customer_pt_id' => $pt_id,
				'customer_telp' => $telepon				
			);
			$this->db->insert('customer', $data); 
			return $this->db->insert_id('customer', $data);
		}
	}	
	
	function check_orang($orang, $telepon){
		$this->db->select("customer_id");
		$this->db->from("customer");
		$this->db->where("customer_name", $orang);
		$this->db->where("customer_telp", $telepon);
		$query = $this->db->get();
		if($query->num_rows() > 0){
			$query2 = $query->result_array();
			$hasil = $query2[0]['customer_id'];
			return $hasil;
		}else{
			$data = array(
		   		'customer_name' => $orang,
				'customer_telp' => $telepon
			);
			$this->db->insert('customer', $data); 
			return $this->db->insert_id('customer', $data);
		}
	}
	function getOwnerList(){
		$this->db->select("*");
		$this->db->from("owner");
		$this->db->order_by("owner_name asc");
		$query = $this->db->get();
		if($query->num_rows()>0){
			$hasil = $query->result_array();
			return $hasil;
		}
	}
	
	function getDriverList(){
		$this->db->select("*");
		$this->db->from("driver");
		$this->db->order_by("gaji asc");
		$query = $this->db->get();
		if($query->num_rows()>0){
			$hasil = $query->result_array();
			return $hasil;
		}
	}
	
	function getCarList($owner_id){
		$this->db->select("*");
		$this->db->from("car");
		$this->db->where("owner_id", $owner_id);
		$this->db->order_by("car_name asc");
		$query = $this->db->get();
		if($query->num_rows()>0){
			$hasil = $query->result_array();
			return $hasil;
		}
	}
	
	function getCarPlat($id){
		$this->db->select("car_license");
		$this->db->from("car");
		$this->db->where("car_id", $id);
		$query = $this->db->get();
		if($query->num_rows()>0){
			$query2 = $query->result_array();
			$hasil = $query2[0]['car_license'];
			return $hasil;
		}
	}
	
	function checkDriverId($nama){
		$this->db->select("driver_id");
		$this->db->from("driver");
		$this->db->where("name", $nama);
		$query = $this->db->get();
		if($query->num_rows()>0){
			$query2 = $query->result_array();
			$hasil = $query2[0]['driver_id'];
			return $hasil;
		}
		else{
			$data = array(
		   		'name' => $nama
			);
			$this->db->insert('driver', $data); 
			return $this->db->insert_id('driver', $data);
		}
	}
	
	function updateSewa($idMobil, $id, $tgl_pinjam, $tgl_kembali){
		$realSewaId = 0;
		$hasil = $this->model_invoice->checkPinjam($idMobil);
		$tgl_pinjam = strtotime($tgl_pinjam);
		$tgl_kembali = strtotime($tgl_kembali);
		foreach($hasil as $hasil1){
			$hasil1['tgl_mulai'] = strtotime($hasil1['tgl_mulai']);
			$hasil1['tgl_akhir'] = strtotime($hasil1['tgl_akhir']);
			if(($tgl_pinjam>=$hasil1['tgl_mulai'])&&($tgl_kembali<=$hasil1['tgl_akhir']))
			{
				$realSewaId = $hasil1['sewa_id'];
				$data = array(
		   			'sewa_id' => $realSewaId,
					'invoice_id' => $id
					);
				$this->db->insert('header_sewa_invoice', $data);
			}			
		}		
	}
	
	function checkPinjam($idMobil){
		$this->db->select("*");
		$this->db->from("sewa");
		$this->db->where("car_id", $idMobil);
		$query = $this->db->get();
		if($query->num_rows()>0){
			$hasil = $query->result_array();
			return $hasil;
		}
	}
	
}